package com.midian.common.utils;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.midian.common.exception.BizException;
import com.midian.common.exception.ModelException;
import com.midian.model.common.VoExcelModel;
/**
 * 
 * @Title:工具类  
 * @Desription:Excel工具类
 * @Company:MDKG
 * @ClassName:ExcelHelper.java
 * @Author:Mengfh,Jiangjb
 * @CreateDate:2013-8-12 下午5:05:57  
 * @UpdateUser:Mengfh
 * @Version:0.2
 */
public class ExcelHelper {
	/**Log4j日志对象*/
	private static final Logger logger = Logger.getLogger(ExcelHelper.class);
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导入Excel
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午3:33:34
	 * @param excelFile Excel文件
	 * @return 返回Excel中第一个sheet的数据
	 */
	public static List<List<String>> readExcel(File excelFile) {
		List<List<List<String>>> excelDataTotalList = readExcelList(excelFile);
		List<List<String>> sheetFirstDataList = new ArrayList<List<String>>();
		if(excelDataTotalList != null && !excelDataTotalList.isEmpty()) {
			//获取Excel的第一个sheet的内容
			sheetFirstDataList = excelDataTotalList.get(0);
		}
		return sheetFirstDataList;
	}
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导入Excel
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午3:33:34
	 * @param excelFile Excel文件
	 * @return 返回Excel中多个sheet的数据
	 */
	public static List<List<List<String>>> readExcelList(File excelFile) {
		if(!excelFile.exists()) {
			return null;
		}
		WorkbookSettings workbookSettings=new WorkbookSettings();
        workbookSettings.setEncoding(System.getProperty("file.encoding")); //设置编码，使用系统的编码
		Workbook workbook = null;
		List<List<List<String>>> excelDataTotalList = new ArrayList<List<List<String>>>();
		try {
			workbook = Workbook.getWorkbook(excelFile, workbookSettings);
			Sheet[] sheets = workbook.getSheets();
			if(sheets != null && sheets.length > 0) {
				for (Sheet sheet : sheets) {
					List<List<String>> rowList = new ArrayList<List<String>>();
					int columnCount = sheet.getColumns();
					int rowCount = sheet.getRows();
					
					for(int i = 0; i < rowCount; i++) {
						List<String> columnList = new ArrayList<String>();
						for (int j = 0; j < columnCount; j++) {
							Cell cell = sheet.getCell(j, i);
							columnList.add(cell.getContents());
						}
						rowList.add(columnList);
					}
					excelDataTotalList.add(rowList);
				}
			}
		} catch (BiffException e) {
			//e.printStackTrace();
		} catch (IOException e) {
			//e.printStackTrace();
		}
		if(workbook != null) {
			workbook.close();
			workbook = null;
		}
		return excelDataTotalList;
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param data 数据
	 * @return
	 */
	public static File writeExcel(String tempSavePath,VoExcelModel excelModel,List data) throws BizException {
		List<List> dataList = new ArrayList<List>();
		dataList.add(data);
		List<VoExcelModel> excelModelList = new ArrayList<VoExcelModel>();
		excelModelList.add(excelModel);
		return writeExcelList(tempSavePath, excelModelList, dataList, null, false);
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param data 数据
	 * @param extraHeadDataTotalList 额外的显示在头部的信息列表
	 * @return
	 */
	public static File writeExcel(String tempSavePath,VoExcelModel excelModel,
			List data, List<List<String>> extraHeadDataTotalList) throws BizException {
		return writeExcel(tempSavePath, excelModel, data, extraHeadDataTotalList, false);
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param data 数据
	 * @param extraHeadDataTotalList 额外的显示在头部的信息列表
	 * @param isMergeFirstExtraHeadData 是否合并额外信息
	 * @return
	 */
	public static File writeExcel(String tempSavePath,VoExcelModel excelModel,
			List data, List<List<String>> extraHeadDataTotalList, boolean isMergeFirstExtraHeadData) throws BizException {
		List<List> dataList = new ArrayList<List>();
		dataList.add(data);
		List<VoExcelModel> excelModelList = new ArrayList<VoExcelModel>();
		excelModelList.add(excelModel);
		return writeExcelList(tempSavePath, excelModelList, dataList, extraHeadDataTotalList, isMergeFirstExtraHeadData);
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:yaoxt
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param dataList 数据
	 * @return
	 */
	public static File writeExcelList(String tempSavePath,List<VoExcelModel> excelModelList,
			List<List> dataList) throws BizException {
		return writeExcelList(tempSavePath, excelModelList, dataList, null, false);
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param dataList 数据
	 * @param extraHeadDataTotalList 额外的显示在头部的信息列表
	 * @return
	 */
	public static File writeExcelList(String tempSavePath,List<VoExcelModel> excelModelList,
			List<List> dataList, List<List<String>> extraHeadDataTotalList) throws BizException {
		return writeExcelList(tempSavePath, excelModelList, dataList, extraHeadDataTotalList, false);
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 导出Excel
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:33:59
	 * @param tempSavePath 保存文件地址 
	 * @param excelModel Excel的Model类
	 * @param dataList 数据
	 * @param extraHeadDataTotalList 额外的显示在头部的信息列表
	 * @param isMergeFirstExtraHeadData 是否合并额外信息
	 * @return
	 */
	public static File writeExcelList(String tempSavePath,List<VoExcelModel> excelModelList,
			List<List> dataList, List<List<String>> extraHeadDataTotalList, boolean isMergeFirstExtraHeadData) throws BizException{
		/*if(data != null && data.size() > 30000) {
			throw new BizException("对不起，导出Excel记录限制为30000条！");
		}*/
		
		if(dataList == null || dataList.isEmpty()) {
			throw new BizException("导出数据为空");
		}
		//所有数据集都为空的标识
		boolean isAllEmpty = true;
		for (List list : dataList) {
			if (list != null && !list.isEmpty()) {
				isAllEmpty = false;
				break;
			}
		}
		if (isAllEmpty) {
			throw new BizException("导出数据为空");
		}
 
		File dir =new File(tempSavePath);
		if(!dir.exists()){
			dir.mkdirs();//如果不存在创建文件夹
		}
		//---------------------生成文件名称开始
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		Date date = new Date();
		StringBuilder fileNameBuilder = new StringBuilder(tempSavePath+"/");
		fileNameBuilder.append(dateFormat.format(date));
		fileNameBuilder.append( (date.getTime()+"").substring(5, 9));
		fileNameBuilder.append(".xls");
		String downFileName=fileNameBuilder.toString();
		dateFormat =  new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		//---------------------生成文件名称结束
		File file = new File(downFileName);
		WritableFont font = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.BOLD,false);//样式,大小,加粗
		WritableCellFormat cell = new WritableCellFormat(font); 
		try {
			cell.setAlignment(jxl.format.Alignment.CENTRE);
		} catch (WriteException e1) {
			e1.printStackTrace();
		}
		WritableFont fontData = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD,false);//样式,大小,加粗
		WritableCellFormat cellData = new WritableCellFormat(fontData); 
		try {
			cellData.setAlignment(jxl.format.Alignment.CENTRE);
			cellData.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		} catch (WriteException e1) {
			e1.printStackTrace();
		}
		//额外数据信息样式
		WritableFont extraDataTitle = new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.BOLD,false);//样式,大小,加粗
		WritableCellFormat extraDataTitleCell = new WritableCellFormat(extraDataTitle); 
		try {
			extraDataTitleCell.setAlignment(jxl.format.Alignment.CENTRE);
		} catch (WriteException e1) {
			e1.printStackTrace();
		}
		try {
			FileOutputStream os = new FileOutputStream(file);
			BufferedOutputStream bos = new BufferedOutputStream(os);
			WritableWorkbook writebook=Workbook.createWorkbook(bos);
			for(int k=0; k<dataList.size();k++) {
				List data=dataList.get(k);
				//创建一个表
				WritableSheet sheet = writebook.createSheet("sheet", k);
				int rowIndex = 0;
				VoExcelModel excelModel=excelModelList.get(k);
				//额外的头信息列表
				if(extraHeadDataTotalList != null && !extraHeadDataTotalList.isEmpty()) {
					for(int i = 0; i < extraHeadDataTotalList.size(); i++) {
						//获取每一行的头信息
						List<String> extraHeadDataList = extraHeadDataTotalList.get(i);
						if(extraHeadDataList != null && !extraHeadDataList.isEmpty()) {
							Integer mergeSize = 0;
							List<String> propertyNameList = excelModel.getPropertyNameCN();
							if(propertyNameList != null && !propertyNameList.isEmpty()) {
								mergeSize = propertyNameList.size();
							}
							
							for(int j = 0; j < extraHeadDataList.size(); j++) {
								//列，行，内容(如果合并额外信息列，使用额外信息样式)
								Label title = new Label(j, i, extraHeadDataList.get(j), isMergeFirstExtraHeadData ? extraDataTitleCell : cellData);
								sheet.addCell(title);
							}
							//合并额外信息列
							if(isMergeFirstExtraHeadData) {
								sheet.mergeCells(0, i, mergeSize - 1, i);
							}
							rowIndex = i + 2; //头信息列表与主标题之间需要空行隔开，下标加2
						}
					}
				}
				//生成列头
				for (int i = 0; i < excelModel.getPropertyNameCN().size(); i++) {
											//列，行，内容(如果有额外头信息，设置新的行下标)
					//由于可能导出内容存在头信息，使用rowIndex设置主标题行的下标
					Label title = new Label(i, rowIndex, excelModel.getPropertyNameCN().get(i),cell);
					sheet.addCell(title);
					sheet.setColumnView(i,  excelModel.getWidth().get(i)); // 设置第 列的宽度			 
				}
				//如果有子集合要求才用到的属性    主对象跨行记录下标
				int writeRowspanIndex=0;
			 
				//生成数据
				for (int i = 0; i < data.size(); i++) {//纵向生成 
						Object ob = data.get(i);
						List sonList=null;
					
						for (int j = 0; j < excelModel.getPropertyName().size(); j++) {//横向生成 
							Object value = null;
							if(ob instanceof Map) {
								//如果对象为Map，获取Map中对应key的值
								value = getPropertyValueByMap(ob, excelModel.getPropertyName().get(j));
							} else {
								//如果对象为Model类对象，通过反射获取该属性的值
								value = getPropertyValue(ob,excelModel.getPropertyName().get(j));
							}
							if(value != null) {
								if(value instanceof Date){
									//如果是日期格式，转换成字符串再导出，解决日期格式不是24小时制的情况
									value = dateFormat.format((Date) value);
								} else if(value instanceof Double && String.valueOf(value).endsWith(".0")) {
									//如果是浮点型，并且值为整数，转换成整形导出
									value = ((Number) value).intValue();
								}
							}
							
							//如果有子集合
							if(excelModel.getSonObjectName()!=null && !"".equals(excelModel.getSonObjectName())){
								sonList =(List) getPropertyValue(ob,excelModel.getSonObjectName());
								 if(sonList!=null && sonList.size()>0){
									   sheet.mergeCells(j, i + rowIndex + 1+writeRowspanIndex,j, i + rowIndex+sonList.size()+writeRowspanIndex);// 1:起始列,2:起始行,3:合并到第几列,4:合并到第几行必须大于等于起始行
								  }
							 } 
							//由于可能导出内容存在头信息，使用rowIndex设置主内容行的下标
							Label rowdata = new Label(j, i + rowIndex + 1+writeRowspanIndex, value==null?"":value+"",cellData);
							sheet.addCell(rowdata);
						 
						}
						//如果输出对象中   子集合有数据
						if(sonList!=null && sonList.size()>0){
							//获取父对象生成到多少列
							int faterCellIndex =  excelModel.getPropertyName().size();
							for (int sonIndex =0 ;sonIndex<sonList.size(); sonIndex++) {//纵向生成
								Object sonData = sonList.get(sonIndex);
								 for (int j =0; j < excelModel.getSonPropertyName().size(); j++) {//横向生成 
										Object value = null;
										if(sonData instanceof Map) {
											//如果对象为Map，获取Map中对应key的值
											value = getPropertyValueByMap(sonData, excelModel.getSonPropertyName().get(j));
										} else {
											//如果对象为Model类对象，通过反射获取该属性的值
											value = getPropertyValue(sonData,excelModel.getSonPropertyName().get(j));
										}
										if(value != null) {
											if(value instanceof Date){
												//如果是日期格式，转换成字符串再导出，解决日期格式不是24小时制的情况
												value = dateFormat.format((Date) value);
											} else if(value instanceof Double && String.valueOf(value).endsWith(".0")) {
												//如果是浮点型，并且值为整数，转换成整形导出
												value = ((Number) value).intValue();
											}
										}
										//由于可能导出内容存在头信息，使用rowIndex设置主内容行的下标
										Label rowdata = new Label(faterCellIndex+j, i + rowIndex + 1+sonIndex+writeRowspanIndex, value==null?"":value+"",cellData);
										sheet.addCell(rowdata);
									}
							}
							//因为起始行号为零开始 所以要减sonlist集合要减一
						 
							writeRowspanIndex = writeRowspanIndex+ sonList.size()-1;
						}
						 
				 }
			}
			writebook.write();
			if (writebook != null) {
				writebook.close();
				os.close();
				bos.close();
			}
		} catch (IOException e) {
			file = null;
			//e.printStackTrace();
		} catch (RowsExceededException e) {
			file = null;
			//e.printStackTrace();
		} catch (WriteException e) {
			file = null;
			//e.printStackTrace();
		}
		return file;
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 获取属性链值
	 * @Author:Jiangjb
	 * @CreateDate:2013-6-7 下午3:35:03
	 * @param obj 目标对象
	 * @param property 属性
	 * @return
	 */
	private static Object getPropertyValue(Object obj,String property){
		String[] propertyChain = property.split("\\.");//获取属性链
		Object result = null;
		for (String pro : propertyChain) {
			if(obj==null){
				break;
			}
			String methodName = "get"+pro.substring(0,1).toUpperCase()+pro.substring(1);
			Class cl = obj.getClass();
			try {
				Method method = cl.getMethod(methodName, null);
				result = method.invoke(obj, null);
				//如果result为日期类型，将日期转换为字符串类型(防止导出后转换为24小时的问题)
				if(result instanceof Date) {
					Date date = (Date) result;
					result = convertDateToStr(date);
				}
				////System.out.println(methodName+":"+result);
				if(result!=null){
					String resultStr = result.toString().trim();
					if(!resultStr.equals("")){
						obj = result;
						continue;
					}
				}
				result = null;
				obj = null;
			} catch (Exception e) {
				//e.printStackTrace();
				throw new ModelException("不存在该方法 "+methodName);
			}
		}
		return result;
	}
	
	/**
	 * 
	 * @ClassName: ExcelHelper.java
	 * @Description: 获取Map中对应key的值
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午3:35:46
	 * @param obj map对象
	 * @param property 属性名称
	 * @return
	 */
	private static Object getPropertyValueByMap(Object obj, String property) {
		Map map = (Map) obj;
		Object val = null;
		if(map != null) {
			val = map.get(property);
			//如果val为日期类型，将日期转换为字符串类型(防止导出后转换为24小时的问题)
			if(val instanceof Date) {
				Date date = (Date) val;
				val = convertDateToStr(date);
			}
		}
		return val;
	}
	
	private static String convertDateToStr(Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return sdf.format(date);
	}
	
	/**
	 * 创建Excel的Model对象
	 * @param propertyNameList 属性名称列表
	 * @param propertyNameCNList 表头名称列表
	 * @param columnWidth 列宽
	 * @return
	 */
	public static VoExcelModel createExcelModel(List<String> propertyNameList, List<String> propertyNameCNList, Integer columnWidth) {
		if(propertyNameList == null || propertyNameCNList == null || columnWidth == null 
				|| propertyNameList.size() != propertyNameCNList.size()) {
			return null;
		}
		//Excel列的列宽列表
		List<Integer> columnWidthList = new ArrayList<Integer>();
		//根据表头个数，设置每一列的列宽(暂定设置每一列为相同列宽)
		for(int i = 0; i < propertyNameList.size(); i++) {
			columnWidthList.add(columnWidth);
		}
		return new VoExcelModel(propertyNameList, propertyNameCNList, columnWidthList);
	}
	
	/**
	 * 创建Excel的Model对象
	 * @param propertyNameArrs 属性名称数组
	 * @param propertyNameCNArrs 表头名称数组
	 * @param columnWidth 列宽
	 * @return
	 */
	public static VoExcelModel createExcelModel(String[] propertyNameArrs, String[] propertyNameCNArrs, Integer columnWidth) {
		if(propertyNameArrs == null || propertyNameCNArrs == null || columnWidth == null 
				|| propertyNameArrs.length != propertyNameCNArrs.length) {
			return null;
		}
		//属性名称列表
		List<String> propertyNameList = Arrays.asList(propertyNameArrs);
		//中文表头列表
		List<String> propertyNameCNList = Arrays.asList(propertyNameCNArrs);
		return createExcelModel(propertyNameList, propertyNameCNList, columnWidth);
	}
	
	/**
	 * 根据浏览器类型返回下载的文件名(用于防止文件名乱码)
	 * @param fileName 文件名
	 * @return
	 */
	public static String getFileNameByUserAgent(String fileName) {
		if (fileName != null) {
			try {
				fileName = URLDecoder.decode(fileName, "UTF-8");
				HttpServletRequest request = ServletActionContext.getRequest();
				String agent = request.getHeader("User-Agent");
				if (null != agent) {
					agent = agent.toLowerCase();
					if (agent.indexOf("firefox") != -1) {
						//火狐
						fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
					} else {
						//其他游览器
						fileName = URLEncoder.encode(fileName, "UTF-8");
					}
					fileName = fileName.replaceAll("\\+", "%20");
				}
			}catch(UnsupportedEncodingException e){
				logger.error(e.getMessage());
				return "";
			}
			return fileName;
		}
		return "";
	}
}
